package org.study.swing.study02.util;

import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;

/**
 * 作用描述
 * 参考 https://www.cnblogs.com/fzf007007/p/14819373.html
 *
 * @author doveylovey
 * @version v1.0.0
 * @email 1135782208@qq.com
 * @date 2024年11月12日
 */
@Slf4j
@UtilityClass
public class DBUtil {
    static {
        try {
            // 加载驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {
        String url = PropertiesUtil.value("url");
        String userName = PropertiesUtil.value("username");
        String password = PropertiesUtil.value("password");
        return DriverManager.getConnection(url, userName, password);
    }

    public void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public void close(Statement statement, Connection connection) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        close(connection);
    }

    public void close(PreparedStatement preparedStatement, Connection connection) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        close(connection);
    }

    public void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        close(statement, connection);
    }

    public void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        close(preparedStatement, connection);
    }

    public boolean executeUpdate(String sql) {
        Connection connection = null;
        Statement statement = null;
        try {
            // 获取连接
            connection = getConnection();
            // 创建语句
            statement = connection.createStatement();
            // 执行SQL语句
            int num = statement.executeUpdate(sql);
            return num > 0;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            // 释放资源
            close(statement, connection);
        }
        return false;
    }

    public void select(String sql, IRowMapper rowMapper) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnection();
            // 创建语句
            statement = connection.createStatement();
            // 执行SQL语句
            resultSet = statement.executeQuery(sql);
            // 处理结果
            rowMapper.rowMapper(resultSet);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            // 释放资源
            close(resultSet, statement, connection);
        }
    }

    public void select(String sql, IRowMapper rowMapper, Object... params) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {
            // 获取连接
            connection = getConnection();
            // 参数预编译
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject((i + 1), params[i]);
            }
            // 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            // 处理结果
            rowMapper.rowMapper(resultSet);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            // 释放资源
            close(resultSet, preparedStatement, connection);
        }
    }

    public boolean exist(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = getConnection();
            // 参数预编译
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject((i + 1), params[i]);
            }
            // 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            // 处理结果
            return resultSet.next();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            // 释放资源
            close(resultSet, preparedStatement, connection);
        }
        return false;
    }

    public boolean update(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = getConnection();
            // 参数预编译
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
            // 执行SQL语句
            int effect = preparedStatement.executeUpdate();
            return effect > 0;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            // 释放资源
            close(preparedStatement, connection);
        }
        return false;
    }

    public boolean batch(String... sqls) {
        Statement statement = null;
        Connection connection = null;
        try {
            // 获取连接
            connection = getConnection();
            connection.setAutoCommit(false);
            // 创建语句
            statement = connection.createStatement();
            for (String sql : sqls) {
                // addVBatch批量处理 一起执行sql语句
                statement.addBatch(sql);
            }
            // 执行SQL语句
            statement.executeBatch();
            connection.commit();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } finally {
            // 释放资源
            close(statement, connection);
        }
        return false;
    }
}
